package sql

import org.apache.kafka.common.serialization.StringDeserializer
import org.apache.spark.SparkConf
import org.apache.spark.streaming.{Seconds, StreamingContext}
import org.apache.spark.streaming.kafka010.ConsumerStrategies.Subscribe
import org.apache.spark.streaming.kafka010.KafkaUtils
import org.apache.spark.streaming.kafka010.LocationStrategies.PreferConsistent
import java.sql.DriverManager

object stuSemestersex_mengjiayi {
  case class personObj(semester: Int, gender: String, count: Int)

  def main(args: Array[String]): Unit = {
    // Spark配置
    val conf = new SparkConf().setMaster("local[*]").setAppName("SparkRealTimeGenderCountToMySQL")
    val ssc = new StreamingContext(conf, Seconds(2))

    ssc.sparkContext.setLogLevel("error")

    // 配置Kafka消费者参数，用于从新的stu读取数据
    val kakaParams = Map[String, Object](
      "bootstrap.servers" -> "192.168.136.128:9092",
      // 这里要和发送数据到stu主题时的Kafka服务器地址一致
      "key.deserializer" -> classOf[StringDeserializer],
      "value.deserializer" -> classOf[StringDeserializer],
      "group.id" -> "niit",
      "enable.auto.commit" -> (false: java.lang.Boolean)
    )

    // 要读取的主题名称，设置为
    val topicName = Array("semeterSex")

    // 从Kafka获取数据流
    val streamRdd = KafkaUtils.createDirectStream[String, String](
      ssc, PreferConsistent,
      Subscribe[String, String](topicName, kakaParams)
    )

    // 处理从Kafka获取的数据，将每行数据按照","拆分并转换为personObj类型的RDD
    val rowRdd = streamRdd.map(_.value()).map(line => {
      val fields = line.split(",")
      personObj(fields(0).toInt, fields(1), fields(2).toInt)
    })

    // 每隔2秒对数据进行统计处理
    rowRdd.foreachRDD(rdd => {
      // 按学期和性别分组并计算每组的数量总和
      val genderCount = rdd.map(person => ((person.semester, person.gender), person.count))
        .reduceByKey(_ + _)

      // 打印当前批次的统计结果（可用于调试查看数据）
      genderCount.foreach(println)

      // 将统计结果推送到MySQL数据库
      genderCount.foreachPartition(partition => {
        val connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/HUEL?characterEncoding=UTF-8&useSSL=false", "root", "123456")
        val statement = connection.createStatement()

        partition.foreach { case ((semester, gender), count) =>
          val sql = s"INSERT INTO testsex (semester, gender, count) VALUES ('$semester', '$gender', $count)"
          statement.executeUpdate(sql)
        }

        statement.close()
        connection.close()
      })
    })

    // 启动流式处理并等待终止
    ssc.start()
    ssc.awaitTermination()
  }
}